USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_CARGA_MAESTRO_CREDITO]    Script Date: 11/24/2011 14:14:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0002_CARGA_MAESTRO_CREDITO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0002_CARGA_MAESTRO_CREDITO]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0002_CARGA_MAESTRO_CREDITO]    Script Date: 11/24/2011 14:14:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[WASP_M0002_CARGA_MAESTRO_CREDITO] (@FECHA_INICIO CHAR(8), @FECHA_TERMINO AS CHAR(8))
AS
BEGIN
	SELECT	moncred =
			CASE
				WHEN (cre.aomda = 999 AND cre.aopap = 2) THEN 'UF'
		        ELSE 'PE'
			END,
			cre.aopre AS uf,
	        cre.aoimp AS monto_prestamo,
	        NULL AS CRAGENCIA,
            CAST(cre.aooper AS DECIMAL) CRCREDITO,
            NULL AS CRMARCA,
            SUBSTRING(rel.pendoc, 1, LEN(rel.pendoc)-1) AS CRRUTDEUDOR,
            SUBSTRING(rel.pendoc, LEN(rel.pendoc),1 ) AS CRDVDEUDOR,
	        rel.pendoc AS RUTDEUDOR,
            NULL AS CRRUTCOD1,
            CONVERT(VARCHAR(1),NULL) AS CRDVECOD1,
            NULL AS CRRUTCOD2,
            CONVERT(VARCHAR(1),NULL) AS CRDVECOD2,
            NULL AS CRRUTEMPRE,
            TIPO_CLIENTE = 
            CASE cre.aomod 
				WHEN 30 THEN 4
                WHEN 32 THEN 3
                ELSE NULL
			END,
            NULL AS CRDVEEMPRE,
            cre.aotope AS CRCLASE,
            COUNT(cuotas.ppcta) AS CRCUOTAS,
            CAST(CONVERT(VARCHAR, coloc.JT73109FAP, 112) AS DECIMAL(8,0)) AS CRFECAPROB,
			NULL AS CRVALCUOTA,
			NULL AS CRDIASPRIM,
			cre.aoimp AS CRMONTO,
			NULL AS CRORIGEN,
			NULL AS CRSEGURO, 
			cre.aotasa AS CRTASA,
			NULL AS CRESPECIAL,
			NULL AS CRMODIFICA,
			NULL AS STSMODREGC,
			NULL AS FMODIFREGC,
			NULL AS USRMODREGC,
			NULL AS CRINDICA,
			NULL AS CRSERIE,
			NULL AS CRNOTARIO,
			NULL AS CRIMPUESTO,
			NULL AS CRCOMISION,
			'1900-01-01' AS CRFECUPAGO,
			NULL AS USRMODAPRO,
			NULL AS CRFECRELIQ,
			NULL AS USRMODRELI,
			'1900-01-01' AS CRFECPRVEN,
			NULL AS CRGASTOPER,
			NULL AS CRMONABONA,
			cre.aostat AS CRESTADO,
			NULL AS CRSEGDESGR,
			CRSTSVGTE = 
       		CASE 
       	  		WHEN (cre.aostat = 99) THEN 1
		        WHEN (cre.aostat = 0) THEN 0
				ELSE 2
      		END,
			CRSTSMORA = 
			CASE
				WHEN (cre.aostat = 17) THEN 1
				ELSE NULL
			END,
			cre.aosuc AS CRAGECOLOC,
			'                   ' + cast(convert(VARCHAR,per.pfffal,112) AS VARCHAR)+ '            ' AS FILLER,
			cancelado =
			CASE 
				WHEN aostat = 99 THEN 'S'
				ELSE 'N'
			END 
	INTO	MaestroCredito 
	FROM    fsd010 AS cre ,
		    fsr008 AS rel ,
			fsd002 AS per,
			fsd601 AS cuotas,
			fsd008 AS a ,
			JT73109 AS coloc
	WHERE	cre.aosbop  = 0 and
			cre.aotasa <> 0 and 
			cre.aooper <> 999999999 and
			cre.Aocta   = rel.Ctnro   and 
			per.pfndoc = rel.pendoc and 
			cre.aooper = cuotas.ppoper and
			cre.aocta  =  cuotas.ppcta and
			cre.Aocta  = a.ctnro  and
			cuotas.D601co = 'S' and
			cre.pgcod = cuotas.pgcod and
			cre.Aomod = cuotas.ppmod and
			cre.Aosuc = cuotas.ppsuc and
			cre.Aomda = cuotas.ppmda and
			cre.Aopap = cuotas.pppap and 
			cre.Aocta = cuotas.ppcta and
			cre.Aooper = cuotas.ppoper and
			cre.Aosbop = cuotas.ppsbop and
			cre.Aotope = cuotas.pptope and
			coloc.JT73109FAP between @FECHA_INICIO and @FECHA_TERMINO and
			cre.Aooper = coloc.JT73109FPA and
			cre.Aosuc = coloc.JT73109SUC   and
			NOT EXISTS (
			SELECT	*
			FROM	as_desistidos AS anu
			WHERE	cre.aocta = anu.hcta and
					cre.aooper = anu.hoper and
					cre.aosuc = anu.hsucur
			)
	GROUP BY cre.Aomda
			,cre.Aopap
			,cre.aopre
			,cre.aoimp
			,cre.Aooper
			,rel.Pendoc
			,cre.Aotope
			,coloc.JT73109FAP
			,cre.Aoimp
			,cre.Aopre
			,cre.Aotasa
			,cre.Aostat
			,cre.Aosuc
			,per.Pfffal
			,cre.aomod
END

GO


